import os
from io import BytesIO
import requests
import openpyxl
from openpyxl import load_workbook
from openpyxl.drawing.image import Image
from openpyxl.utils import get_column_letter
from openpyxl.utils import units
from openpyxl.styles import NamedStyle, Font, Border, Side, PatternFill, Alignment

os.chdir(os.path.dirname(__file__))

PICTURE_CONVERT_CONFIG =  ['width', 'height', 'font_width']
IMAGE_SUFFIX = ['.jpg', '.jpeg', '.png', '.gif']

class PY_EXCEL_OPENPYXL:
    def __init__(self, excel_address=None, sheet=None) -> None:
        """
            excel_address: excel地址，文件不存在则新创建
            sheet：要读取的sheet整数，从0开始
        当excel_address文件存在且sheet不为None才读取已有文件，否则创建新文件
        """
        self.excel_address = excel_address
        self.sheet = sheet
        self._get_sht()
    
    def _get_sht(self):
        if self.excel_address and os.path.exists(self.excel_address) and self.sheet is not None:
            # 读取一个已存在的excel
            self.wb = load_workbook(self.excel_address)
            self.sht = self.wb.worksheets[self.sheet]
        else:
            # 创建一个新的工作簿和工作表
            self.wb = openpyxl.Workbook()
            self.sht = self.wb.active

    def _is_picture(self, pic_path, check_img=True):
        """判断图片地址是否是图片
        """
        if check_img:
            return str(os.path.splitext(pic_path)[-1]).lower() in IMAGE_SUFFIX
        else:
            return True

    def add_image(self, pic_path, row=1, col=1, width=100, height=30, font_width=units.BASE_COL_WIDTH):
        """向excel中插入图片
            pic_path: 图片路径，支持http,https在线图片
            row: 图片要插入的行，从1开始
            col: 图片要插入的列，从1开始
            width: 要设置的图片宽度
            height: 要设置的图片高度
            font_width: 字符像素，一般为7-9。若出现导出的图片宽度和excel单元格宽度不合，可适当调整该值
        
        """
        column_letter = get_column_letter(col)

        try:
            if str(pic_path).lower().startswith('http'):
                # 下载图片
                response = requests.get(pic_path)
                if response.status_code != 200:
                    raise Exception(f'图片下载失败:{response}')

                img_data = BytesIO(response.content)
                # 使用Pillow打开图片
                img = Image(img_data)
            else:
                img = Image(pic_path)
            
            img.width = width
            img.height = height

            # 计算列宽（单位：字符宽度），假设每个字符宽度为7像素
            column_width_in_characters = img.width / font_width
            self.sht.column_dimensions[column_letter].width = column_width_in_characters

            # 计算行高（单位：点），假设1点约等于1.4像素
            # row_height_in_points = img.height * (72 / DPI)  # 96 DPI 是常见的屏幕分辨率
            row_height_in_points = units.pixels_to_points(img.height)

            self.sht.row_dimensions[row].height = row_height_in_points
            
            # 插入图片位置 A3, C5
            self.sht.add_image(img, f'{column_letter}{row}')
        except Exception as ex:
            self.sht[f'{column_letter}{row}'] = f'Error:{ex}'

    def get_bytes(self):
        """获取文件的字节流，可直接用于网络传输，上传到oss
        无需保存到本地，方便网络传输
        """    
        wbook_bytes = BytesIO()
        self.wb.save(wbook_bytes)
        return wbook_bytes

    def add_data(self, data, convert=None):
        """
        data = [
            ['Name', 'Age', 'City'],
            ['Alice', 28, 'New York'],
            ['Bob', 34, 'Los Angeles'],
            ['Charlie', 22, 'Chicago'],
            ['David', 29, 'Houston']
        ]
         增加图片和表头配置项
        convert = {
            'picture': {
                'index_list': [3, 5]  # 图片地址所在的列数，从0开始
            },
            'header_style': {
                'font': {
                    # 'name':'微软雅黑',  # 字体
                    'size':15,  # 字号
                    'bold':True,  # 加粗
                    'italic':True,  # 斜体
                    'vertAlign':'baseline',  # 垂直对齐方式：subscript, superscript, baseline
                    'underline':'none',  # 下划线：doubleAccounting, single, double,singleAccounting
                    'strike':False,  # 删除线
                    'color':'ff0000'  # aRGB值/十六进制RGB
                },
                'fill': {
                    'fill_type':'lightDown',  # 填充类型：'darkDown', 'lightDown', 'lightTrellis', 'darkGrid', 'darkVertical', 'darkTrellis', 'mediumGray', 'gray0625', 'solid', 'lightGray', 'lightHorizontal', 'darkUp', 'darkGray', 'lightVertical', 'darkHorizontal', 'gray125', 'lightGrid', 'lightUp
                    'start_color':'FF0000',  # 开始颜色
                    'end_color':'E4CF6A'  # 结束颜色
                },
                'alignment': {
                    'horizontal':'center',  # 水平对齐方式：'center', 'right', 'general', 'fill', 'distributed', 'left', 'centerContinuous', 'justify'
                    'vertical':'center',  # 垂直对齐方式：'bottom', 'center', 'distributed', 'justify', 'top'
                    'text_rotation':0,  # 字体旋转角度
                    'wrap_text':True,  # 内容较多时是否自动换行
                    'shrink_to_fit':True,  #如果内容超出单元格宽度是否缩小以适应
                    'indent':0  # 缩减字符
                },
                'border': {
                    'style':'thin',  # 边框样式：'mediumDashDotDot', 'mediumDashed', 'thick', 'dashDot', 'dashDotDot', 'medium', 'thin', 'dotted', 'mediumDashDot', 'slantDashDot', 'double', 'dashed', 'hair
                    'color': 'ff0000'
                }
            }
        }

        """
        # 表头配置项
        header_style = None

        # 图片配置项
        picture_list= []
        picture = {}
        check_img = True

        if convert:
            # 图片配置项
            if 'picture' in convert:
                picture_config = convert['picture']
                picture_list = picture_config.get('index_list', [])
                check_img = picture_config.get('check_img', True)
                # add_image 支持的配置项
                picture = {k: v for k, v in picture_config.items() if k in PICTURE_CONVERT_CONFIG}

            if 'header_style' in convert:
                header_style_config = convert['header_style']
                # 字体配置
                header_style_font = Font(**header_style_config['font']) if 'font' in header_style_config else None
                # 边框配置
                border = Side(**header_style_config['border']) if 'border' in header_style_config else None
                header_style_border = Border(start=border, end=border, left=border, right=border, top=border, bottom=border) if border else None
                # 填充
                header_style_fill = PatternFill(**header_style_config['fill']) if 'fill' in header_style_config else None
                # 位置
                header_style_alignment=Alignment(**header_style_config['alignment']) if 'alignment' in header_style_config else None

                if any([header_style_font, header_style_border, header_style_fill, header_style_alignment]):
                    header_style = NamedStyle(name="header_style")
                    if header_style_font:
                        header_style.font = header_style_font
                    if header_style_border:
                        header_style.border = header_style_border
                    if header_style_fill:
                        header_style.fill = header_style_fill
                    if header_style_alignment:
                        header_style.alignment = header_style_alignment
                
                # 工作表添加样式名称
                if header_style:
                    self.wb.add_named_style(header_style)


        # # 将数据写入工作表
        for row, d in enumerate(data):
            # 先将当前行中的图片链接暂存后替换为空字符
            pictur_info = []
            for pic_i in picture_list:
                if d[pic_i]:
                    # 如果是最后一列可以同时存在多张图片
                    if ';' in d[pic_i] and pic_i == len(d)-1:
                        pic_list = d[pic_i].split(';')
                        for p_i, p in enumerate(pic_list):
                            if self._is_picture(p, check_img):
                                 pictur_info.append({ 'row': row+1, 'col': pic_i+1+p_i, 'pic_path': p })
                                 d[pic_i] = ''

                    elif self._is_picture(d[pic_i], check_img):
                        pictur_info.append({ 'row': row+1, 'col': pic_i+1, 'pic_path': d[pic_i] })
                        d[pic_i] = ''

            # 将移除图片链接的数据插入到excel中
            self.sht.append(d)

            # 将图片插入到excel中
            if pictur_info:
                for pic_data in pictur_info:
                    # 增加图片相关的配置项
                    pic_data.update(picture)
                    self.add_image(**pic_data)

        # 表头样式
        if header_style:
            for cell in self.sht[1]:
                cell.style='header_style'

    def save(self, path=None):
        """保存文件
            path保存的地址
        """
        self.wb.save(self.excel_address or path)


if __name__ == '__main__':
    # 读取已有文件
    # py_excel = PY_EXCEL_OPENPYXL(r'C:\Users\刘\Desktop\t.xlsx', 0)
    # py_excel.add_image(r"G:\test\geyue_20241203105646_1.png", 3,4)
    # py_excel.save()

    # 新建文件
    # py_excel = PY_EXCEL_OPENPYXL()
    # pic_path = 'https://fancyqube-download.oss-cn-shanghai.aliyuncs.com/comments_images/%E5%AE%8B%E5%A2%9E%E5%A2%9E/songzengzeng_20241203105121_1.png'
    # py_excel.add_image(pic_path, 5,4)
    # py_excel.save(r"C:\Users\刘\Desktop\t2.xlsx")

    # 获取文件的字节流
    # 方便在网络中传输，避免保存在本地再从本地读取的过程
    # py_excel = PY_EXCEL_OPENPYXL()
    # pic_path = 'https://fancyqube-download.oss-cn-shanghai.aliyuncs.com/comments_images/%E5%AE%8B%E5%A2%9E%E5%A2%9E/songzengzeng_20241203105121_1.png'
    # py_excel.add_image(pic_path, 3,3, 500, 400)
    # bytes_data = py_excel.get_bytes()

    # with open(r"C:\Users\刘\Desktop\t3.xlsx", 'wb') as f:
    #     f.write(bytes_data.getvalue())

    # # 批量插入数据
    # data = [
    #         ['Name', 'Age', 'City'],
    #         ['Alice', 28, 'New York'],
    #         ['Bob', 34, 'Los Angeles'],
    #         ['Charlie', 22, 'Chicago'],
    #         ['David', 29, 'Houston']
    #     ]
    # py_excel = PY_EXCEL_OPENPYXL()
    # py_excel.add_data(data)
    # py_excel.save(r"C:\Users\刘\Desktop\t4.xlsx")

    # 批量插入带有图片的数据
    # 若是图片在中间字段这只允许一张图片
    # 若是图片在最后一个字段，则允许英文逗号(;)分割多张图片，导出时会向后延申并排展示
    # data = [
    #         ['Name', 'Age', 'City', '图片', '性别', '多张图片'],
    #         ['Alice', 28, 'New York', '', '男', 'https://fancyqube-download.oss-cn-shanghai.aliyuncs.com/comments_images/%E5%AE%8B%E5%A2%9E%E5%A2%9E/songzengzeng_20241203105121_1.png;https://fancyqube-download.oss-cn-shanghai.aliyuncs.com/comments_images/%E5%AE%8B%E5%A2%9E%E5%A2%9E/songzengzeng_20241203105121_1.png'],
    #         ['Bob', 34, 'Los Angeles', 'https://fancyqube-download.oss-cn-shanghai.aliyuncs.com/comments_images/%E5%AE%8B%E5%A2%9E%E5%A2%9E/songzengzeng_20241203105121_1.png', '女', '测试多张图片'],
    #         ['Charlie', 22, 'Chicago', '错误地址', '男', 'https://fancyqube-download.oss-cn-shanghai.aliyuncs.com/comments_images/%E5%AE%8B%E5%A2%9E%E5%A2%9E/songzengzeng_20241203105121_1.png;https://fancyqube-download.oss-cn-shanghai.aliyuncs.com/comments_images/%E5%AE%8B%E5%A2%9E%E5%A2%9E/songzengzeng_20241203105121_1.png;https://fancyqube-download.oss-cn-shanghai.aliyuncs.com/comments_images/%E5%AE%8B%E5%A2%9E%E5%A2%9E/songzengzeng_20241203105121_1.png'],
    #         ['David', 29, 'Houston','https://fancyqube-download.oss-cn-shanghai.aliyuncs.com/comments_images/%E5%AE%8B%E5%A2%9E%E5%A2%9E/songzengzeng_20241203105121_1.png', '', '']
    #     ]
    # py_excel = PY_EXCEL_OPENPYXL()
    # # 图片配置项
    # convert = {
    #         'picture': {
    #             'index_list': [3, 5]
    #         }
    #     }
    # py_excel.add_data(data, convert)
    # py_excel.save(r"C:\Users\刘\Desktop\t4.xlsx")

    # 增加表头样式设置
    data = [
            ['Name', 'Age', 'City', '测试标题字符过多的情况', '性别', '多张图片'],
            ['Alice', 28, 'New York', '', '男', 'https://fancyqube-pic.oss-cn-shanghai.aliyuncs.com/PIC/010.%E6%88%B7%E5%A4%96%E8%BF%90%E5%8A%A8/PE-%E9%98%B2%E6%8A%A4%E7%94%A8%E5%93%81/PE2898/1601PX/PE2898_ft2.jpg;https://fancyqube-download.oss-cn-shanghai.aliyuncs.com/comments_images/%E5%AE%8B%E5%A2%9E%E5%A2%9E/songzengzeng_20241203105121_1.png'],
            ['Bob', 34, 'Los Angeles', 'https://fancyqube-download.oss-cn-shanghai.aliyuncs.com/comments_images/%E5%AE%8B%E5%A2%9E%E5%A2%9E/songzengzeng_20241203105121_1.png', '女', '测试多张图片'],
            ['Charlie', 22, 'Chicago', '错误地址', '男', 'https://fancyqube-download.oss-cn-shanghai.aliyuncs.com/comments_images/%E5%AE%8B%E5%A2%9E%E5%A2%9E/songzengzeng_20241203105121_1.png;https://fancyqube-download.oss-cn-shanghai.aliyuncs.com/comments_images/%E5%AE%8B%E5%A2%9E%E5%A2%9E/songzengzeng_20241203105121_1.png;https://fancyqube-download.oss-cn-shanghai.aliyuncs.com/comments_images/%E5%AE%8B%E5%A2%9E%E5%A2%9E/songzengzeng_20241203105121_1.png'],
            ['David', 29, 'Houston','https://fancyqube-download.oss-cn-shanghai.aliyuncs.com/comments_images/%E5%AE%8B%E5%A2%9E%E5%A2%9E/songzengzeng_20241203105121_1.png', '', 'https://fancyqube-download.oss-cn-shanghai.aliyuncs.com/comments_images/%E8%91%9B%E8%B7%83/geyue_20241203105537_1.png']
        ]
    py_excel = PY_EXCEL_OPENPYXL()
    # 图片配置项
    convert = {
            'picture': {
                'index_list': [3, 5]  # 图片地址所在的列数，从0开始
            },
            # 'header_style': {
            #     'font': {
            #         # 'name':'微软雅黑',  # 字体
            #         'size':15,  # 字号
            #         'bold':True,  # 加粗
            #         'italic':True,  # 斜体
            #         'vertAlign':'baseline',  # 垂直对齐方式：subscript, superscript, baseline
            #         'underline':'none',  # 下划线：doubleAccounting, single, double,singleAccounting
            #         'strike':False,  # 删除线
            #         'color':'ff0000'  # aRGB值/十六进制RGB
            #     },
            #     'fill': {
            #         'fill_type':'lightDown',  # 填充类型：'darkDown', 'lightDown', 'lightTrellis', 'darkGrid', 'darkVertical', 'darkTrellis', 'mediumGray', 'gray0625', 'solid', 'lightGray', 'lightHorizontal', 'darkUp', 'darkGray', 'lightVertical', 'darkHorizontal', 'gray125', 'lightGrid', 'lightUp
            #         'start_color':'FF0000',  # 开始颜色
            #         'end_color':'E4CF6A'  # 结束颜色
            #     },
            #     'alignment': {
            #         'horizontal':'center',  # 水平对齐方式：'center', 'right', 'general', 'fill', 'distributed', 'left', 'centerContinuous', 'justify'
            #         'vertical':'center',  # 垂直对齐方式：'bottom', 'center', 'distributed', 'justify', 'top'
            #         'text_rotation':0,  # 字体旋转角度
            #         'wrap_text':True,  # 内容较多时是否自动换行
            #         'shrink_to_fit':True,  #如果内容超出单元格宽度是否缩小以适应
            #         'indent':0  # 缩减字符
            #     },
            #     'border': {
            #         'style':'thin',  # 边框样式：'mediumDashDotDot', 'mediumDashed', 'thick', 'dashDot', 'dashDotDot', 'medium', 'thin', 'dotted', 'mediumDashDot', 'slantDashDot', 'double', 'dashed', 'hair
            #         'color': 'ff0000'
            #     }
            # }
        }
    py_excel.add_data(data, convert)
    py_excel.save("./test.xlsx")